<%@ page import="java.sql.*" %>
<%@ page import="java.lang.*" %>
<%@ page import="java.io.*" %>
<%
try {
String connectionURL = "jdbc:mysql://webproject2.cse.ust.hk/BookwarmMobile?user=BookwarmMobile&password=bookwarm";

Connection connection = null;

Class.forName("com.mysql.jdbc.Driver").newInstance();

connection = DriverManager.getConnection(connectionURL);

if(!connection.isClosed()) {
String newAccountName = null;
String newCopyID = null;
String newCopyCondition = null;
String newCopyPrice = null;

newAccountName = request.getParameter("username");
newCopyID = request.getParameter("copyid");
newCopyCondition = request.getParameter("copycondition");
newCopyPrice = request.getParameter("copyprice");

int count_user = 0;
Statement statement0 = null;
ResultSet rs0 = null;
statement0 = connection.createStatement();
String query0 = "SELECT count(*) AS Count FROM Users WHERE AccountName = '" + newAccountName + "';";
rs0 = statement0.executeQuery(query0);
if(rs0.next()) {
count_user = Integer.parseInt(rs0.getString("Count"));

}
statement0.close();

if(count_user == 0) {
out.print("user_not_logged_in");

}
else {
int count_bought = 0;
Statement statement1 = null;
ResultSet rs1 = null;
statement1 = connection.createStatement();
String query1 = "SELECT count(*) AS Count FROM Buy WHERE AccountName = '" + newAccountName + "' AND CopyID = " + newCopyID + " AND Status = 'Confirmed';";
rs1 = statement1.executeQuery(query1);
if(rs1.next()) {
count_bought = Integer.parseInt(rs1.getString("Count"));

}
statement1.close();

if(count_bought == 0) {
out.print("modify_to_sell_failed");

}
else {
Statement statement2 = null;
statement2 = connection.createStatement();
String query2 = "UPDATE BookCopies SET Availability = 'BUY' WHERE CopyID = " + newCopyID + ";";
statement2.executeUpdate(query2, Statement.NO_GENERATED_KEYS);
query2 = "UPDATE BookCopies SET CopyCondition = '" + newCopyCondition + "' WHERE CopyID = " + newCopyID + ";";
statement2.executeUpdate(query2, Statement.NO_GENERATED_KEYS);
query2 = "UPDATE BookCopies SET CopyPrice = " + newCopyPrice + " WHERE CopyID =  " + newCopyID + ";";
statement2.executeUpdate(query2, Statement.NO_GENERATED_KEYS);
statement2.close();

Statement statement3 = null;
statement3 = connection.createStatement();
String query3 = "UPDATE Buy SET Status = 'Canceled' WHERE AccountName = '" + newAccountName + "' AND CopyID = " + newCopyID + ";";
statement3.executeUpdate(query3, Statement.NO_GENERATED_KEYS);
statement3.close();

int count_update = 0;
Statement statement4 = null;
statement4 = connection.createStatement();
String query4 = "UPDATE Sell SET Status = 'Listed' WHERE AccountName = '" + newAccountName + "' AND CopyID = " + newCopyID + ";";
count_update = statement4.executeUpdate(query4, Statement.NO_GENERATED_KEYS);
statement4.close();

if(count_update == 0) {
Statement statement5 = null;
statement5 = connection.createStatement();
String query5 = "INSERT INTO Sell Values (DEFAULT, '" + newAccountName + "', " + newCopyID + ", DEFAULT, DEFAULT);";
statement5.executeUpdate(query5, Statement.NO_GENERATED_KEYS);
statement5.close();

}

out.print("modify_to_sell_successful");

}

}

}

connection.close();

}
catch(Exception ex){
out.print(ex);

} 
%>